"""
Excel是老牌办公软件 , 用来存放用例数据也是比较方便的 .
主流的操作 Excel 的库有: openpyxl , pandas
一般从 Excel 读取数据 , 会处理成  列表嵌套字典  或者  列表嵌套对象  的形式
"""
import openpyxl


class DoExcel:

    def __init__(self, excel_name, sheet_name):
        self.excel_name = excel_name
        self.sheet_name = sheet_name

    def read(self):
        wb = openpyxl.load_workbook(self.excel_name)
        sheet = wb[self.sheet_name]
        li_head = []
        li_data = []
        datas = []
        for i in range(1, sheet.max_row + 1):
            if i == 1:
                for j in range(1, sheet.max_column + 1):
                    li_head.append(sheet.cell(i, j).value)
            else:
                li_data.clear()
                for j in range(1, sheet.max_column + 1):
                    li_data.append(sheet.cell(i, j).value)
                data_dict = dict(zip(li_head, li_data))
                datas.append(data_dict)
        return datas

    def read_obj(self):
        def get_value(cell):
            return cell.value

        wb = openpyxl.load_workbook(self.excel_name)
        sheet = wb[self.sheet_name]
        datas = []
        rows = list(sheet.rows)
        li_head = list(map(get_value, rows[0]))

        for row in rows[1:]:
            li_data = list(map(get_value, row))
            data_zip = zip(li_head, li_data)
            excel_data = ExcelData(data_zip)
            datas.append(excel_data)
        return datas

    def write(self, row, column, value):
        wb = openpyxl.load_workbook(self.excel_name)
        sheet = wb[self.sheet_name]
        sheet.cell(row, column).value = value
        try:
            wb.save(self.excel_name)
        except PermissionError:
            print('Excel未打开状态,数据写入失败')


class ExcelData:
    def __init__(self, data_zip):
        for data_tuple in data_zip:
            setattr(self, data_tuple[0], data_tuple[1])


if __name__ == '__main__':

    from os.path import dirname, join

    name_excel = join(dirname(dirname(__file__)) + '/', 'datas/excel_data.xlsx')
    name_sheet = 'DataDemo'
    excel_obj = DoExcel(name_excel, name_sheet)

    data_s = excel_obj.read()
    print(data_s)

    data_s = excel_obj.read_obj()
    for item in data_s:
        print(item.__dict__)
